/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Business;

import Objects.Employee;
import Objects.Product;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Nguyen Kim Manh
 */
public class ProductBusiness {

     /**
     * Method getAllProduct() used to get all product from database
     *
     * @return Product list
     */
    public static ArrayList<Product> getAllProduct() {
        ArrayList<Product> list = new ArrayList<>();

        String sql = "{call sp_getAllProduct}";
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, null);
            while (rs.next()) {
                Product p = new Product();
                p.setProductID(rs.getString("ProductID"));
                p.setProductName(rs.getString("ProductName"));
                p.setProductPrice(rs.getFloat("ProductPrice"));
                p.setProductQuantities(rs.getInt("ProductQuantities"));
                p.setProductUnit(rs.getString("ProductUnits"));
                p.setCategoryID(rs.getString("CategoryID"));
                p.setVendorID(rs.getString("VendorID"));
                 p.setDescription(rs.getString("Description"));
                list.add(p);
            }

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;
    }

    /**
     * Method getProductByCategory() used to get all product from database
     *
     * @param categoryID
     * @return ArrayList<Product>
     */
    public static ArrayList<Product> getProductByCategory(String cate) {
        ArrayList<Product> list = new ArrayList<>();

        String sql = "{call sp_getProductByCategory(?)}";
        ArrayList list1 = new ArrayList();
        list1.add(cate);
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, list1);
            while (rs.next()) {
                Product p = new Product();
                p.setProductID(rs.getString("ProductID"));
                p.setProductName(rs.getString("ProductName"));
                p.setProductPrice(rs.getFloat("ProductPrice"));
                p.setProductQuantities(rs.getInt("ProductQuantities"));
                p.setProductUnit(rs.getString("ProductUnits"));
                p.setCategoryID(rs.getString("CategoryID"));
                p.setVendorID(rs.getString("VendorID"));
                p.setDescription(rs.getString("Description"));
                list.add(p);
            }

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;
    }
    
    /**
     * Method getProductByVendor() used to get all product from database
     *
     * @return ArrayList<Product>
     */
    public static ArrayList<Product> getProductByVendor(String vendor) {
        ArrayList<Product> list = new ArrayList<>();

        String sql = "{call sp_getProductByVendor(?)}";
        ArrayList list1 = new ArrayList();
        list1.add(vendor);
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, list1);
            while (rs.next()) {
                Product p = new Product();
                p.setProductID(rs.getString("ProductID"));
                p.setProductName(rs.getString("ProductName"));
                p.setProductPrice(rs.getFloat("ProductPrice"));
                p.setProductQuantities(rs.getInt("ProductQuantities"));
                p.setProductUnit(rs.getString("ProductUnits"));
                p.setCategoryID(rs.getString("CategoryID"));
                p.setVendorID(rs.getString("VendorID"));
                p.setDescription(rs.getString("Description"));
                list.add(p);
            }

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;
    }

    /**
     * method update product
     */
    public static int updateProduct(Product p) {
       Connection cnn=null;
       int kt = 0;
        try {
             cnn= DBAccess.DB_Connect.getConnection();
            
            String sql = "{call sp_updateProduct(?,?,?,?,?,?,?,?)}";
   
            
            CallableStatement csm=cnn.prepareCall(sql);
            csm.setString(1, p.getProductID());
            csm.setString(2, p.getProductName());
            csm.setString(3, p.getProductUnit());
            csm.setFloat(4, p.getProductPrice());
            csm.setInt(5, p.getProductQuantities());
            csm.setString(6, p.getCategoryID());
            csm.setString(7, p.getVendorID());
            csm.setString(8, p.getDescription());
            
            kt=csm.executeUpdate();

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ProductBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(ProductBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            if(cnn!=null){
                try {
                    cnn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ProductBusiness.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return kt;
    } 
        
        
        /**
         * method used to add new Product
         *
         */
    public static int addProduct(Product p) {
        int kt = 0;

        Connection cnn = null;
        try {
            cnn = DBAccess.DB_Connect.getConnection();
            String sql = "{call sp_addProduct(?,?,?,?,?,?,?,?)}";
            CallableStatement csm = cnn.prepareCall(sql);

            csm.setString(1, p.getProductID());
            csm.setString(2, p.getProductName());
            csm.setString(3,p.getProductUnit());
            csm.setFloat(4,p.getProductPrice());
            csm.setInt(5, p.getProductQuantities());
            csm.setString(6, p.getCategoryID());
            csm.setString(7, p.getVendorID());
            csm.setString(8, p.getDescription());

            kt = csm.executeUpdate();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cnn != null) {
                try {
                    cnn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ProductBusiness.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        return kt;
    }

    /**
     * method used to get Product by Name
     */
    public static ArrayList<Product> getProductByName(String name) {

        ArrayList<Product> list = new ArrayList<>();
        String sql = "{call sp_getProductByName(?)}";
        ArrayList list1 = new ArrayList();
        list1.add(name);
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, list1);
            while (rs.next()) {
                Product p = new Product();
                p.setProductID(rs.getString("ProductID"));
                p.setProductName(rs.getString("ProductName"));
                p.setProductPrice(rs.getFloat("ProductPrice"));
                p.setProductQuantities(rs.getInt("ProductQuantities"));
                p.setProductUnit(rs.getString("ProductUnits"));
                p.setCategoryID(rs.getString("CategoryID"));
                p.setVendorID(rs.getString("VendorID"));
                 p.setDescription(rs.getString("Description"));
                list.add(p);
            }

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;

    }

    /**
     * method used to get Product by ID
     */
    public static ArrayList<Product> getProductByID(String id) {

        ArrayList<Product> list = new ArrayList<>();
        String sql = "{call sp_getProductByID(?)}";
        ArrayList list1 = new ArrayList();
        list1.add(id);
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, list1);
            while (rs.next()) {
                Product p = new Product();
                p.setProductID(rs.getString("ProductID"));
                p.setProductName(rs.getString("ProductName"));
                p.setProductPrice(rs.getFloat("ProductPrice"));
                p.setProductQuantities(rs.getInt("ProductQuantities"));
                p.setProductUnit(rs.getString("ProductUnits"));
                p.setCategoryID(rs.getString("CategoryID"));
                p.setVendorID(rs.getString("VendorID"));
                 p.setDescription(rs.getString("Description"));
                list.add(p);
            }

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;

    }

    /**
     * Method getNewestProduct() used to get product has max ProductID from database
     *
     * @return Product object
     */
    public static Product getNewestProduct() {
        Product p=new Product();

        String sql = "{call sp_getNewestProduct}";
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, null);
            if (rs.next()) {
                p.setProductID(rs.getString("ProductID"));
                p.setProductName(rs.getString("ProductName"));
                p.setProductPrice(rs.getFloat("ProductPrice"));
                p.setProductQuantities(rs.getInt("ProductQuantities"));
                p.setProductUnit(rs.getString("ProductUnits"));
                p.setCategoryID(rs.getString("CategoryID"));
                p.setVendorID(rs.getString("VendorID"));
                 p.setDescription(rs.getString("Description"));
            }

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }

        return p;
    }
    
    /**
     * method check the existence of product name
     * @return value has boolean type
     */
    public static int checkProductName(String name){
        int check=0;
        
        String sql="{call sp_checkProductName(?)}";
        ArrayList list=new ArrayList();
        list.add(name);
        try {
            ResultSet rs=DBAccess.DB_Connect.executeQuery(sql, list);
            if(rs.next()){
                check=1;
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ProductBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(ProductBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }  
        
        return check;
    }
    
    /**
     * method get sale order that constraint with product 
     * if result not null then product cannot be deleted
     */
    public static boolean checkConstrainWithSaleOrder(String productID){
        boolean check=false;
        
        String sql="{call sp_checkConstraintProductWithSaleOder(?)}";
        ArrayList list=new ArrayList();
        list.add(productID);
        try {
            ResultSet rs=DBAccess.DB_Connect.executeQuery(sql, list);
            if(rs.next()){
                check=true;
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ProductBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(ProductBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return check;
    }
    
    /**
     * method get purchase order that constraint with product 
     * if result not null then product cannot be deleted
     */
    public static boolean checkConstrainWithPurchaseOrder(String productID){
        boolean check=false;
        
        String sql="{call sp_checkConstraintProductWithPurchaseOrder(?)}";
        ArrayList list=new ArrayList();
        list.add(productID);
        try {
            ResultSet rs=DBAccess.DB_Connect.executeQuery(sql, list);
            if(rs.next()){
                check=true;
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ProductBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(ProductBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return check;
    }
    
    /**
     * method used to delete product
     */
    public static int deleteProduct(String product){
        int kq=0;
        
        String sql="{call sp_deleteProduct(?)}";
        ArrayList list=new ArrayList();
        list.add(product);
        try {
            kq=DBAccess.DB_Connect.executeNonQuery(sql, list);
        } catch (SQLException ex) {
            Logger.getLogger(ProductBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ProductBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return kq;
    }
}
